﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using System.Data;

namespace eyeGuitarsWebService
{
    /// <summary>
    /// Summary description for eyeGuitarsWS
    /// </summary>
    [WebService(Namespace = "http://eyeguitarswebservice.apphb.com/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class eyeGuitarsWS : System.Web.Services.WebService
    {

        #region Variables
        private SqlConnection con = null;
        private SqlTransaction trx = null;
        private string guitarList;

        #region user variables
        private int userID;
        private string firstName;
        private string surName;
        private string email;
        private string userName;
        private string password;
        #endregion

        #region guitar variables
        private string g_ID;
        private string g_name;
        private string g_brand;
        private string g_type; //Electric or Acoustic
        private string g_instrument; //Bass or Guitar
        private int g_nr_of_strings;
        private string g_color;
        private decimal g_price;
        private string g_description;
        private string g_img;
        #endregion

        #endregion        

        [WebMethod]
        public string getGuitarsByInstrument(string _instrumentType)
        {
            try
            {                
                //Open database connection using our connectionclass
                con = ConnectionClass.GetConnection();

                //Create a transaction for the connection
                trx = con.BeginTransaction("getGuitarsByInstrument");

                //Call the stored procedure
                SqlCommand cmd = new SqlCommand("getGuitarsByInstrument", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Transaction = trx;

                //Stored procedure parameters
                cmd.Parameters.AddWithValue("@instrumentType", _instrumentType);

                //Create a datareader and instantiate it
                SqlDataReader rdr = null;
                rdr = cmd.ExecuteReader();

                //Fill a list with data from the database
                while (rdr.Read())
                {
                    //guitarList.Add(rdr["g_ID"].ToString() + "|" + rdr["g_name"].ToString());
                    guitarList += rdr["g_ID"].ToString() + "|" + rdr["g_name"].ToString() + "~";
                }

                return guitarList;

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                //Check if the connection is still open, and close it if needed
                if (con != null)
                {
                    con.Close();
                }
            }
        }

        [WebMethod]
        public string getGuitarByName(string _g_name)
        {
            try
            {
                //Open database connection using our connectionclass
                con = ConnectionClass.GetConnection();

                //Create a transaction for the connection
                trx = con.BeginTransaction("getGuitarByName");

                //Call the stored procedure
                SqlCommand cmd = new SqlCommand("getGuitarByName", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Transaction = trx;

                //Stored procedure parameters
                cmd.Parameters.AddWithValue("@g_name", _g_name);

                //Create a datareader and instantiate it
                SqlDataReader rdr = null;
                rdr = cmd.ExecuteReader();

                //Fill the reader with data from the database
                while (rdr.Read())
                {
                    g_ID = rdr["g_ID"].ToString();
                    g_name = rdr["g_name"].ToString();
                    g_brand = rdr["g_brand"].ToString();
                    g_type = rdr["g_type"].ToString(); //Electric or Acoustic
                    g_instrument = rdr["g_instrument"].ToString(); //Bass or Guitar
                    g_nr_of_strings = Convert.ToInt32(rdr["g_nr_of_strings"].ToString());
                    g_color = rdr["g_color"].ToString();
                    g_price = Convert.ToDecimal(rdr["g_price"].ToString());
                    g_description = rdr["g_description"].ToString();
                    g_img = rdr["g_img"].ToString();
                }

                return g_ID + "|" + g_name + "|" + g_brand + "|" + g_type + "|" + g_nr_of_strings
                    + "|" + g_color + "|" + g_price + "|" + g_description + "|" + g_img;

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                //Check if the connection is still open, and close it if needed
                if (con != null)
                {
                    con.Close();
                }
            }
        }

        [WebMethod]
        public string getLoginData(string _userName)
        {
            try
            {
                //Open database connection using our connectionclass
                con = ConnectionClass.GetConnection();

                //Create a transaction for the connection
                trx = con.BeginTransaction("getLoginData");

                //Call the stored procedure
                SqlCommand cmd = new SqlCommand("getLoginData", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Transaction = trx;

                //Stored procedure parameters
                cmd.Parameters.AddWithValue("@userName", _userName);

                //Create a datareader and instantiate it
                SqlDataReader rdr = null;
                rdr = cmd.ExecuteReader();

                //Fill the reader with data from the database
                while (rdr.Read())
                {
                    password = rdr["password"].ToString();
                }
                return password;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                //Check if the connection is still open, and close it if needed
                if (con != null)
                {
                    con.Close();
                }
            }
        }

        [WebMethod]
        public string getUserData(string _username)
        {
            try
            {
                //Open database connection using our connectionclass
                con = ConnectionClass.GetConnection();

                //Create a transaction for the connection
                trx = con.BeginTransaction("getUserData");

                //Call the stored procedure
                SqlCommand cmd = new SqlCommand("getUserData", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Transaction = trx;

                //Stored procedure parameters
                cmd.Parameters.AddWithValue("@userName", _username);

                //Create a datareader and instantiate it
                SqlDataReader rdr = null;
                rdr = cmd.ExecuteReader();

                //Fill the reader with data from the database
                while (rdr.Read())
                {
                    userID = Convert.ToInt32(rdr["userID"].ToString());
                    firstName = rdr["firstName"].ToString();
                    surName = rdr["surName"].ToString();
                    email = rdr["email"].ToString();
                    userName = rdr["userName"].ToString();
                    password = rdr["password"].ToString();                    
                }

                return userID + "|" + firstName + "|" + surName + "|" + email + "|" + userName + "|" + password;

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                //Check if the connection is still open, and close it if needed
                if (con != null)
                {
                    con.Close();
                }
            }
        }

        [WebMethod]
        public bool insertNewUser(string _firstName, string _surName, string _userName, string _password, string _email)
        {
            try
            {
                //Open database connection using our connectionclass
                con = ConnectionClass.GetConnection();

                //Create a transaction for the connection
                trx = con.BeginTransaction("insertNewUser");

                //Call the stored procedure
                SqlCommand cmd = new SqlCommand("insertNewUser", con);
                cmd.CommandType = CommandType.StoredProcedure;

                //Create a SHA-2 password hash based on password and username. We're using the SHA512 algorithm
                string hashedPassword = CalculateSHA_2.CreateSHAHash(_password, _userName);

                //Add values to the stored procedure
                
                cmd.Parameters.AddWithValue("@firstName", _firstName);
                cmd.Parameters.AddWithValue("@surName", _surName);
                cmd.Parameters.AddWithValue("@userName", _userName);
                cmd.Parameters.AddWithValue("@password", hashedPassword);
                cmd.Parameters.AddWithValue("@email", _email);

                //Set the transaction
                cmd.Transaction = trx;

                //Execute the stored procedure
                cmd.ExecuteNonQuery();

                Console.WriteLine("<h1>success</h1>");

                //Commit the query
                trx.Commit();
            }
            catch (Exception ex)
            {
                if (trx != null)
                {
                    trx.Rollback();
                }
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //Check if con is not emty, and close it if needed
                if (con != null)
                {
                    con.Close();
                }
            }

            return true;
        }

        [WebMethod]
        public string calculateSHA_2(string _password, string _salt)
        {
            return CalculateSHA_2.CreateSHAHash(_password, _salt);
        }

    }
}
